
--EXEC SP_UPDATE_PART_NUMBER_COMMENTS 3


ALTER PROC SP_UPDATE_PART_NUMBER_COMMENTS
@BASE_PRODUCT_ID INT
AS
BEGIN
	DECLARE @PROCESS_STEP_ID INT
	DECLARE @PROCESS_TYPE VARCHAR(20)
	DECLARE @BASE_PART_NUMBER VARCHAR(50)
	DECLARE @PARTS_CURSOR CURSOR
	DECLARE @COMMENTS VARCHAR(300)
	DECLARE @PACKAGE_TYPE VARCHAR(200)
        DECLARE @ASSM_PROCESSSTEP_ID INT
	SELECT @BASE_PART_NUMBER = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @BASE_PRODUCT_ID
	SET @PARTS_CURSOR = CURSOR FOR SELECT P.PROCESSSTEP_ID, PS_TYPE_NAME FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.P_PARENT_PRODUCT_ID = @BASE_PRODUCT_ID AND PS.PS_TYPE_NAME NOT LIKE ('Finished Goods')
	OPEN @PARTS_CURSOR
	FETCH NEXT FROM @PARTS_CURSOR INTO @PROCESS_STEP_ID, @PROCESS_TYPE
	WHILE @@FETCH_STATUS = 0
	BEGIN
	    -- for process types, 'fab', 'wafer store', 'sort' and 'die bank', base product name is treated as comments
	    -- for 'assembly', 'test' and 'class store', basep product name + package type is treated as comments
	    -- note : for 'test' and 'class store', 'package type' of the 'assembly' part number is used
	    -- for 'finished goods' no comments are stored here
		IF( @PROCESS_TYPE = 'FAB' OR @PROCESS_TYPE = 'Wafer Store' OR @PROCESS_TYPE = 'Sort' OR @PROCESS_TYPE = 'Die Bank')
		BEGIN
			SET @COMMENTS = @BASE_PART_NUMBER
		END
		ELSE IF( @PROCESS_TYPE = 'Assembly')
		BEGIN
			SET @PACKAGE_TYPE = NULL
			SELECT @PACKAGE_TYPE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND PARAMETER_KEY LIKE ('Package ID')
			SET @COMMENTS = @BASE_PART_NUMBER + '/' + LTRIM(RTRIM(@PACKAGE_TYPE))
		END
		ELSE IF( @PROCESS_TYPE = 'TEST' OR @PROCESS_TYPE = 'Class Store' )
		BEGIN
			SET @PACKAGE_TYPE = NULL
			SELECT @ASSM_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID( @PROCESS_STEP_ID ) WHERE PROCESS_TYPE_NAME LIKE 'ASSEMBLY'
			SELECT @PACKAGE_TYPE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @ASSM_PROCESSSTEP_ID AND PARAMETER_KEY LIKE ('Package ID')
			SET @COMMENTS = @BASE_PART_NUMBER + '/' + LTRIM(RTRIM(@PACKAGE_TYPE))
		END

		UPDATE PRODUCT SET P_COMMENTS = @COMMENTS WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID

		FETCH NEXT FROM @PARTS_CURSOR INTO @PROCESS_STEP_ID, @PROCESS_TYPE
	END
END